﻿--1 MANAGERS
SELECT	   
	CASE 
	WHEN	INDEX(AE_ASSIGNED,'(') >0 
	THEN SUBSTR(AE_ASSIGNED, 1, INDEX(AE_ASSIGNED,'(') -2) 
ELSE	AE_ASSIGNED 
END	AREA_MGR,  AREA_MGR AS ACCT_MGR, 1 AS LVL, 1 AS ORD, market_area
FROM	       SMARTMOVES.TXSM101_SO_SCORE
GROUP	BY market_area, AREA_MGR
UNION	
SELECT	    	
	CASE 
	WHEN	INDEX(AE_ASSIGNED,'(') >0 
	THEN SUBSTR(AE_ASSIGNED, 1, INDEX(AE_ASSIGNED,'(') -2) 
ELSE	AE_ASSIGNED
END AREA_MGR,
	CASE 
	WHEN	INDEX(AM,'(') >0 
	THEN SUBSTR(AM, 1, INDEX(AM,'(') -2) 
ELSE	AM  
END ACCT_MGR,
2 AS LVL, 1 AS ORD, market_area
FROM	        SMARTMOVES.TXSM101_SO_SCORE
ORDER	BY 5, 4, 1, 3, 2
;
--2 STATES
SELECT	DISTINCT  
CASE	WHEN TRIM(market_area) IN ('AUSTIN/SA','DALLAS/FO','HOUSTON/S','WEST TEXA') THEN 'TEXAS' 
	WHEN	 TRIM(market_area)  IN ('ORANGE CO','BAY AREA','FRESNO','LOS ANGEL','SACRAMENT','SAN DIEGO') THEN 'CALIFORNIA' 
	WHEN	 TRIM(market_area)  IN ('KANSAS CI') THEN 'KANSAS' 
	WHEN	 TRIM(market_area)  IN ('ST. LOUIS') THEN 'MISSOURI' 
ELSE	market_area 
END	TXT, 

CASE SUBSTR(TXT,1,5)
WHEN 'CONNE' THEN 'CT'
WHEN 'TEXAS' THEN 'TX'
WHEN 'GEORG' THEN 'GA'
WHEN 'KANSA' THEN 'KS'
WHEN 'MISSO' THEN 'MO'
WHEN 'KENTU' THEN 'KY'
WHEN 'MISSI' THEN 'MS'
WHEN 'NORTH' THEN 'NC'
WHEN 'NEVAD' THEN 'NV'
WHEN 'SOUTH' THEN 'SC'
WHEN 'TENNE' THEN 'TN'
ELSE SUBSTR(TXT,1,2)
END VAL
FROM	SMARTMOVES.TXSM101_SO_SCORE
UNION ALL 
SELECT 
	' ALL' TXT, 
	'ZZ' VAL 
FROM 
	SYS_CALENDAR.CALENDAR 
WHERE 
	CALENDAR_DATE=CURRENT_DATE 
ORDER BY 1
;
--3 PORTFOLIOS
SELECT     PORTFOLIO_NAME AS TXT, PORTFOLIO_NAME AS VAL
FROM         SMARTMOVES.TXSM101_SO_SCORE
GROUP BY PORTFOLIO_NAME
ORDER BY TXT
;
--4 MARKETS
SELECT	DISTINCT  market_area TXT, TXT AS VAL
FROM        SMARTMOVES.TXSM101_SO_SCORE
GROUP BY market_area
ORDER BY TXT
;
--5 REGIONS
SELECT	DISTINCT  REGION TXT, TXT AS VAL
FROM        SMARTMOVES.TXSM101_SO_SCORE
UNION ALL 
SELECT 
	' ALL' TXT, 
	'ZZ' VAL 
FROM 
	SYS_CALENDAR.CALENDAR 
WHERE 
	CALENDAR_DATE=CURRENT_DATE 
ORDER BY 1
;
--6 PROPERTIES
SELECT  
   TRIM(STATE_CD)||TRIM(PROPERTY_ID) TRAC_ID,
	PROPERTY_ID TRAC_NUM,
	TRIM(REGION) REGION, 
	CASE	WHEN TRIM(market_area) IN ('AUSTIN/SA','DALLAS/FO','HOUSTON/S','WEST TEXA') THEN 'TEXAS' 
	WHEN	 TRIM(market_area)  IN ('ORANGE CO','BAY AREA','FRESNO','LOS ANGEL','SACRAMENT','SAN DIEGO') THEN 'CALIFORNIA' 
	WHEN	 TRIM(market_area)  IN ('KANSAS CI') THEN 'KANSAS' 
	WHEN	 TRIM(market_area)  IN ('ST. LOUIS') THEN 'MISSOURI' 
	ELSE	TRIM(market_area) 
	END	STATE, 
	CASE SUBSTR(STATE,1,5)
	WHEN 'CONNE' THEN 'CT'
	WHEN 'TEXAS' THEN 'TX'
	WHEN 'GEORG' THEN 'GA'
	WHEN 'KANSA' THEN 'KS'
	WHEN 'MISSO' THEN 'MO'
	WHEN 'KENTU' THEN 'KY'
	WHEN 'MISSI' THEN 'MS'
	WHEN 'NORTH' THEN 'NC'
	WHEN 'NEVAD' THEN 'NV'
	WHEN 'SOUTH' THEN 'SC'
	WHEN 'TENNE' THEN 'TN'
	ELSE SUBSTR(STATE,1,2)
	END STATE_CD,
   TRIM(MARKET_AREA) MARKET,
   TRIM(AE_ASSIGNED) AREA_MGR,
   TRIM(AM) ACCT_MGR,
   TRIM(PORTFOLIO_NAME) PORTFOLIO_NM,
   TRIM(PROPERTY_NAME) PROPERTY_NM,
   	1 ADDRESSES, 
	1 GREENS, 
	1 SUBSCRIBERS, 
	1 HSIA, 
	1 IPTV, 
	1 VOIP, 
	1 HSIA_REVENUE, 
	1 IPTV_REVENUE, 
	1 VOIP_REVENUE, 
	1 OTHER_UVERSE_REVENUE, 
	1 TOTAL_REVENUE,
	100*GREENS /(ADDRESSES + 0.01) LIT,
	100*SUBSCRIBERS/(ADDRESSES + 0.01) INSERVICE,
	100*SUBSCRIBERS /(GREENS + 0.01)  SATURATION,
	100*IPTV_REVENUE /(TOTAL_REVENUE + 0.01) IPTVPercentage,
	100*HSIA_REVENUE /(TOTAL_REVENUE + 0.01) HSIAPercentage,
	100*VOIP_REVENUE /(TOTAL_REVENUE + 0.01)  VOIPPercentage,
	100*OTHER_UVERSE_REVENUE /(TOTAL_REVENUE + 0.01)  OtherPercentage,
	TOTAL_REVENUE /(SUBSCRIBERS + 0.01)  TotalPerSubscriber,
	IPTV_REVENUE /(IPTV + 0.01) IPTVPerSubscriber,
	HSIA_REVENUE /(HSIA + 0.01) HSIAPerSubscriber,
	VOIP_REVENUE /(VOIP + 0.01) VOIPPerSubscriber,
	OTHER_UVERSE_REVENUE /(SUBSCRIBERS + 0.01)  OtherPerSubscriber
FROM
SMARTMOVES.TXSM101_SO_SCORE